﻿CREATE PROCEDURE [maint].[GetTableFragmentation]
@DbName [sysname], @TableName [sysname], @ContigResultsCursor CURSOR VARYING OUTPUT, @OutputDiagnostics BIT=0
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @RC int
	SET @RC = 0

	IF(@OutputDiagnostics = 1)
		BEGIN
			PRINT OBJECT_NAME(@@PROCID)
			PRINT N'	@DbName = N''' + ISNULL(@DbName,N'NULL') + ''''
			PRINT N'	@TableName = N''' + ISNULL(@TableName,N'NULL') + ''''
		END

	DECLARE @ContigResultsTblName nvarchar(36)
	EXEC @RC = maint.CreateContigResultsTable @ContigResultsTblName OUTPUT
	IF(@RC <> 0) GOTO EXIT_PROC

	DECLARE @InsertContigStmntInDbContext nvarchar(512)
	SET @InsertContigStmntInDbContext = N'
		INSERT [' + @ContigResultsTblName + N']
		EXEC [' + @DbName + '].dbo.sp_executesql N''DBCC SHOWCONTIG(''''' + @TableName + N''''') WITH FAST, ALL_INDEXES, TABLERESULTS, NO_INFOMSGS'''
	EXEC @RC = sp_executesql @InsertContigStmntInDbContext
	IF(@RC <> 0) GOTO EXIT_PROC

	DECLARE @OpenCursorStmnt nvarchar(512)
	SET @OpenCursorStmnt = N'
		SET @ContigResultsCursor = CURSOR FAST_FORWARD FOR (
			SELECT
				''[''+ RTRIM([IndexName]) + '']'' AS IndexName
				,[LogicalFrag]
				,[CountPages]
			FROM  [' + @ContigResultsTblName + N']
			WHERE ([IndexId] BETWEEN 1 AND 254)
		)
		OPEN @ContigResultsCursor'

	EXEC @RC = sp_executesql @OpenCursorStmnt
		,N'@ContigResultsCursor CURSOR OUTPUT'
		,@ContigResultsCursor OUTPUT
	IF(@RC <> 0) GOTO EXIT_PROC

EXIT_PROC:
	RETURN(@RC)
END